C:/Projekte/RAIS/dataaccesslayer/SearchManagement.cs

Go to the documentation of this file.


using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

using RAIS.Common;

namespace RAIS.DataAccessLayer
{
    public class SearchManagement
        {
                //*********************************************************************
                #region Public static methods

                public static Dictionary<string, string> GetSearchItems()
                {
                        Dictionary<string, string> searchItems = new Dictionary<string, string>();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetSearchItems, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        searchItems.Add(sqlReader["Search Item"].ToString()
                                                                , sqlReader["Search Item"].ToString());
                                                }
                                        }
                                }
                        }
                        return searchItems;
                }
                public static Dictionary<string, SearchDefinition> GetSearchDefinitionsBySearchItem(string searchItem)
                {
                        Dictionary<string, SearchDefinition> searchDefinitions = new Dictionary<string, SearchDefinition>();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetSearchDefinitionsBySearchItem, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SearchItem, searchItem);

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        SearchDefinition searchDefinition = new SearchDefinition(sqlReader);
                                                        searchDefinitions.Add(searchDefinition.SearchField, searchDefinition);
                                                }
                                        }
                                }
                        }
                        return searchDefinitions;
                }
                public static bool SearchInDB(Search search, ref string resultMessage)
                {
                        bool success = true;

                        Dictionary<int, string> searchResults = new Dictionary<int, string>();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                try
                                {
                                        using (SqlCommand sqlCommand = new SqlCommand(search.SearchDefinition.SearchSQL, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.Text;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SearchText, search.SearchText);
                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserID, search.UserID);

                                                using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                                {
                                                        while (sqlReader.Read())
                                                        {
                                                                if (sqlReader.FieldCount < 2
                                                                        || sqlReader[0].GetType().Name != "Int32")
                                                                {
                                                                        resultMessage = Common.Messages.WRONG_SEARCH_SELECT;
                                                                        success = false;
                                                                }
                                                                else
                                                                {
                                                                        searchResults.Add((int)sqlReader[0], sqlReader[1].ToString());
                                                                }
                                                        }
                                                }
                                        }
                                        search.SearchResult = searchResults;
                                }
                                catch (Exception e)
                                {
                    resultMessage = e.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                        success = false;
                                }
                        }
                        return success;
                }

        public static void AddSearchItem(string searchItem, string searchField, string menu, string mask, int dynamicMask, string searchSQL)
        {
            using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand("usp_AddSearchItem", sqlConnection))
                {
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    DataAccessUtilities.AddParameter(sqlCommand, "@SearchItem", searchItem);
                    DataAccessUtilities.AddParameter(sqlCommand, "@SearchField", searchField);
                    DataAccessUtilities.AddParameter(sqlCommand, "@Menu", menu);
                    DataAccessUtilities.AddParameter(sqlCommand, "@Mask", mask);
                    DataAccessUtilities.AddParameter(sqlCommand, "@DynamicMask", dynamicMask);
                    DataAccessUtilities.AddParameter(sqlCommand, "@SearchSQL", searchSQL);

                    sqlCommand.ExecuteNonQuery();

                }
            }
        }

        public static void DeleteSearchItem(int itemID)
        {
            using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(
                                                        string.Format("DELETE FROM [System - Search] WHERE PK_SQL_ID = {0}", itemID)
                                                        , sqlConnection))
                {
                    sqlCommand.CommandType = System.Data.CommandType.Text;
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }
        #endregion
        }
}